package com.anhuanjia.tools.eao.controller;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.efastsoft.framework.UIFacade;
import com.efastsoft.framework.annotation.Alias;
import com.efastsoft.framework.annotation.Priv;
import com.efastsoft.framework.annotation.Priv.LoginType;
import com.efastsoft.framework.data.DataRow;
import com.efastsoft.framework.data.DataTable;
import com.efastsoft.framework.data.Q;
import com.efastsoft.framework.ui.control.CommonDataGridAction;
import com.efastsoft.framework.utility.StringUtil;

@Alias("visit")
public class VisitUI extends UIFacade {
	/**
	 * 后台公示统计
	 * 
	 * @throws ParseException
	 */
	@Priv(loginType = LoginType.ALL)
	public void visit() throws ParseException {
		String Account = $V("Account");
		String MProvince = $V("MProvince");// 省份
		String MCity = $V("MCity");// 城市
		String ProjectType = $V("ProjectType");// 项目类型(一次公示，二次公示...)
		String StartTime = $V("StartTime");// 起始时间
		String EndTime = $V("EndTime");// 结束时间
		Q q = new Q("select count(p.Id),SUM(p.VisitNumber) from eao_publicity p left join Ec_Member as mh on mh.Id = p.Operator left join mh_resource as mr on mr.Member = p.Operator left join crm_company as co on mh.ID = co.ID or mr.Company = co.ID left join Ec_Member as em on co.ID = em.ID and em.Type = 'Ent'  where 1 = 1 and p.State = '2' and p.IsDelete = 0");
		Q qt = new Q("select p.Id,p.ProjectType from eao_publicity p left join Ec_Member as mh on mh.Id = p.Operator left join mh_resource as mr on mr.Member = p.Operator left join crm_company as co on mh.ID = co.ID or mr.Company = co.ID left join Ec_Member as em on co.ID = em.ID and em.Type = 'Ent'  where 1 = 1 and p.State = '2' and p.IsDelete = 0");
		if (StringUtil.isNotEmpty(MProvince)) {
			q.append(" and p.MProvince = ?", MProvince);
			qt.append(" and p.MProvince = ?", MProvince);
		}
		if (StringUtil.isNotEmpty(MCity)) {
			q.append(" and p.MCity = ?", MCity);
			qt.append(" and p.MCity = ?", MCity);
		}
		if (StringUtil.isNotEmpty(ProjectType)) {
			q.append(" and p.ProjectType = ?", ProjectType);
			qt.append(" and p.ProjectType = ?", ProjectType);
		}
		if (StringUtil.isNotEmpty(Account)) {
			if("非体系内账号".equals(Account)){
				q.append(" and (p.Operator not in (select Member from mh_resource ) and p.Operator not in(select cc.ID from crm_company cc left join ec_member em on cc.ID = em.ID where em.Type = 'Ent' ))");
				qt.append(" and (p.Operator not in (select Member from mh_resource ) and p.Operator not in(select cc.ID from crm_company cc left join ec_member em on cc.ID = em.ID where em.Type = 'Ent' ))");
			}else{
				String [] arr = Account.split(",");
				q.and().append(" (p.Operator in (select Member from mh_resource where Company in  (select ID from ec_member where");
				q.in("Mobile", arr);
				q.append("))");
				q.append(" or p.Operator in (select em.ID from ec_member em left join crm_company cc on em.ID = cc.ID where");
				q.in("em.Mobile", arr);
				q.append("))");
				
				qt.and().append(" (p.Operator in (select Member from mh_resource where Company in (select ID from ec_member where");
				qt.in("Mobile", arr);
				qt.append("))");
				qt.append(" or p.Operator in (select em.ID from ec_member em left join crm_company cc on em.ID = cc.ID where");
				qt.in("em.Mobile", arr);
				qt.append("))");
			}
		}
		if(StringUtil.isNotEmpty(StartTime)){
			q.and().append(" DATE_FORMAT(p.PublicityTime,'%Y-%m-%d')>=?", StartTime);
		}
		if(StringUtil.isNotEmpty(EndTime)){
			q.and().append(" DATE_FORMAT(p.PublicityTime,'%Y-%m-%d')<=?", EndTime);
		}

		Integer counteq = 0;
		DataTable dataTable = qt.fetch();
		for (DataRow dataRow : dataTable) {
			DataTable table = new Q("select count(1) as num from eao_publicity_enclosure where PublicityId = ?",
					dataRow.getString("Id")).fetch();
			if (table.get(0).getInt("num")>0) {
				counteq++;
			}
		}
		DataTable dt = q.fetch();
		String countId = dt.get(0, 0).toString();
		String SUMvisit = "";
		if (dt.get(0, 1) == null) {
			SUMvisit = "0";
		} else {
			SUMvisit = dt.get(0, 1).toString();
		}
		this.Response.put("countId", countId);
		this.Response.put("SUMvisit", SUMvisit);
		this.Response.put("counteq", counteq);

	}

	/**
	 * 后台公示日访问量统计(折线图)
	 * 
	 * @throws ParseException
	 */
	@Priv(loginType = LoginType.ALL)
	public void publicityVisit() throws ParseException {
		String Account = $V("Account");
		String StartTime = $V("StartTime");// 起始时间
		String EndTime = $V("EndTime");// 结束时间
		String MProvince = $V("MProvince");// 省份
		String MCity = $V("MCity");// 城市
		String ProjectType = $V("ProjectType");// 项目类型(一次公示，二次公示...)
		Q q = new Q(" select DATE_FORMAT(p.PublicityTime,'%Y-%m-%d') as PublicityTime , count(p.PublicityTime) as num,sum(p.VisitNumber) as VisitNumber from eao_publicity p left join Ec_Member as mh on mh.Id = p.Operator left join mh_resource as mr on mr.Member = p.Operator left join crm_company as co on mh.ID = co.ID or mr.Company = co.ID left join Ec_Member as em on co.ID = em.ID and em.Type = 'Ent'  where 1 = 1 and p.State = '2' and p.IsDelete = 0");
		if(StringUtil.isNotEmpty(StartTime)){
			q.and().append(" DATE_FORMAT(p.PublicityTime,'%Y-%m-%d')>=?", StartTime);
		}
		if(StringUtil.isNotEmpty(EndTime)){
			q.and().append(" DATE_FORMAT(p.PublicityTime,'%Y-%m-%d')<=?", EndTime);
		}
		if (StringUtil.isNotEmpty(MProvince)) {
			q.append(" and p.MProvince = ?", MProvince);
		}
		if (StringUtil.isNotEmpty(MCity)) {
			q.append(" and p.MCity = ?", MCity);
		}
		if (StringUtil.isNotEmpty(ProjectType)) {
			q.append(" and p.ProjectType = ?", ProjectType);
		}
		if (StringUtil.isNotEmpty(Account)) {
			if("非体系内账号".equals(Account)){
				q.append(" and (p.Operator not in (select Member from mh_resource ) and p.Operator not in(select cc.ID from crm_company cc left join ec_member em on cc.ID = em.ID where em.Type = 'Ent' ))");
			}else{
				String [] arr = Account.split(",");
				q.and().append(" (Operator in (select Member from mh_resource where Company in  (select ID from ec_member where");
				q.in("Mobile", arr);
				q.append("))");
				q.append(" or Operator in (select em.ID from ec_member em inner join crm_company cc on em.ID = cc.ID where");
				q.in("em.Mobile", arr);
				q.append("))");
			}
		}
		q.append(" GROUP BY DATE_FORMAT(p.PublicityTime,'%Y-%m-%d')");
		DataTable dtz = q.fetch();
		List<Map<String, String>> lists = new ArrayList<>();
		for (DataRow dataRow : dtz) {
			Map<String, String> mapOne = new HashMap<>();
			mapOne.put("date", dataRow.getString("PublicityTime"));
			mapOne.put("count", dataRow.getString("num"));
			mapOne.put("VisitNumber", dataRow.getString("VisitNumber"));
			lists.add(mapOne);
		}

		this.Response.put("publicity_time", lists);

	
	}

	/**
	 * @Title: findProjectVisit
	 * @Description: 项目统计
	 * @returnLong
	 * @author wangdingding
	 * @throws ParseException
	 * @date 2018年10月13日下午3:36:55
	 */
	@Priv(loginType = LoginType.ALL)
	public void findProjectVisit(CommonDataGridAction cdg) throws ParseException {
		String StartTime = $V("StartTime");// 起始时间
		String EndTime = $V("EndTime");// 结束时间
		String Member = $V("Member");// 机构
		Q q = new Q("select ty.Company,GROUP_CONCAT(ty.c) as ReportType , coalesce(crm.FullName,'非体系内账号') as FullName,ty.PerjectTime,crm.AddTime,crm.Address,coalesce(member.Mobile,'非体系内账号') CompanyMobile,crm.Address,sum(ty.Count) as Count from (select PerjectTime,Company, concat(coalesce(ReportType ,'无分类') , COUNT(*)) as c , count(*) as count,CreatePerson from eao_project_info where 1 = 1");

		if(StringUtil.isNotEmpty(StartTime)){
			q.and().append(" DATE_FORMAT(PerjectTime,'%Y-%m-%d') >= ? ", StartTime);
		}
		if(StringUtil.isNotEmpty(EndTime)){
			q.and().append(" DATE_FORMAT(PerjectTime,'%Y-%m-%d') <= ? ", EndTime);
		}
		q.append(" GROUP BY ReportType,Company) as ty  left join Crm_Company as crm on crm.ID = ty.Company left join ec_member member on crm.ID = member.ID where 1 = 1");
		if (StringUtil.isNotEmpty(Member)) {
			if("非体系内账号".equals(Member)){
				q.and().append(" (ty.Company not in (select ID from ec_member where type = 'Ent'))");
			}else{
				String[] company = Member.split(",");
				q.and().in("member.Mobile", company);	
			}
		}
		q.append(" GROUP BY ty.Company order by ty.PerjectTime desc");
		DataTable dt = q.fetch(cdg);
		cdg.bindData(dt);
		cdg.setTotal(dt.getRowCount());

	}

	@Priv(loginType = LoginType.ALL)
	public void findProjectVisitV() throws ParseException {
		String StartTime = $V("StartTime");// 起始时间
		String EndTime = $V("EndTime");// 结束时间
		String Member = $V("Member");// 机构
		Q q = new Q(" select ty.Company ,count(ty.Company) as count , coalesce(crm.FullName,'非体系内账号') FullName from eao_project_info as ty left join Crm_Company as crm on crm.ID =ty.Company left join ec_member member on crm.ID = member.ID where 1= 1");
		Q q5 = new Q(" select DATE_FORMAT(ty.PerjectTime,'%Y-%m-%d') as PerjectTime ,count(DATE_FORMAT(ty.PerjectTime,'%Y-%m-%d')) as count from  eao_project_info ty left join Crm_Company as crm on crm.ID = ty.Company left join ec_member member on crm.ID = member.ID where 1= 1 ");
		if(StringUtil.isNotEmpty(StartTime)){
			q.and().append(" DATE_FORMAT(ty.PerjectTime,'%Y-%m-%d') >= ? ", StartTime);
			q5.and().append(" DATE_FORMAT(ty.PerjectTime,'%Y-%m-%d') >= ? ", StartTime);
		}
		if(StringUtil.isNotEmpty(EndTime)){
			q.and().append(" DATE_FORMAT(ty.PerjectTime,'%Y-%m-%d') <= ? ", EndTime);
			q5.and().append(" DATE_FORMAT(ty.PerjectTime,'%Y-%m-%d') <= ? ", EndTime);
		}
		if (StringUtil.isNotEmpty(Member)) {
			if("非体系内账号".equals(Member)){
				q.and().append(" (ty.Company not in (select ID from ec_member where type = 'Ent'))");
				q5.and().append(" (ty.Company not in (select ID from ec_member where type = 'Ent'))");
			}else{
				String[] company = Member.split(",");
				q.and().in("member.Mobile", company);	
				q5.and().in("member.Mobile", company);	
			}
		}
		q.append(" GROUP BY ty.Company ");
		q5.append(" group by DATE_FORMAT(ty.PerjectTime,'%Y-%m-%d') ");
		DataTable dt5 = q5.fetch();
		List<Map<String, String>> lists = new ArrayList<>();
		for (DataRow dataRow : dt5) {
			Map<String, String> mapOne = new HashMap<>();
			String AddTime = dataRow.get(0).toString();
			String count = dataRow.get(1).toString();
			mapOne.put("date", AddTime);
			mapOne.put("count", count);
			lists.add(mapOne);
		}
		this.Response.put("num", q.fetch());// 机构项目占总项目比例（饼图）
		this.Response.put("countAddTime", lists);// 数量——时间（折线图，柱状图）
	}
	
	/**
	 * 一级机构账号，名称
	 * 
	 * @throws ParseException
	 */
	@Priv(loginType = LoginType.ALL)
	public void companyInfo() throws ParseException{
		DataTable q = new Q("select cc.ID ,cc.FullName,em.Mobile from Ec_Member as em left join  Crm_Company as cc on em.id = cc.id join ahj_member_remark as amr on amr.ID = em.ID and amr.RemarkMember like '%eao%' where em.Type = 'Ent' order by em.AddTime desc").fetch();
		List<Map<String, String>> lists = new ArrayList<>();
		for (DataRow dataRow : q) {
			if(StringUtil.isNotEmpty(dataRow.getString("Mobile"))){
				Map<String, String> mapOne = new HashMap<>();
				mapOne.put("id", dataRow.getString("Mobile"));
				mapOne.put("text", dataRow.getString("Mobile")+"("+dataRow.getString("FullName")+")");
				lists.add(mapOne);
			}
		}
		String strTrue = "";
		for (DataRow dataRow : q) {
			if(StringUtil.isNotEmpty(dataRow.getString("Mobile"))){
				strTrue += dataRow.getString("Mobile") + ",";
			}
		}
		this.Response.put("info", lists);
		this.Response.put("strTrue", strTrue);
	}
	
	/**
	 * 一级账号统计
	 * 
	 * @throws ParseException
	 */
	@Priv(loginType = LoginType.ALL)
	public void findAccountVisit(CommonDataGridAction dga) throws ParseException {
		String StartTime = $V("StartTime");// 起始时间
		String EndTime = $V("EndTime");// 结束时间
		String MProvince = $V("MProvince");// 省份
		String MCity = $V("MCity");// 城市
		String FullName = $V("FullName");// 机构
		Q q = new Q("select em.*,cc.FullName , cc.Address as adres from Ec_Member as em Left join Crm_Company as cc on em.id = cc.id join ahj_member_remark as amr on amr.ID = em.ID and amr.RemarkMember like '%eao%' where em.Type = 'Ent'");
		
		if(StringUtil.isNotEmpty(StartTime)){
			q.and().append(" DATE_FORMAT(em.AddTime,'%Y-%m-%d')>=?", StartTime);
		}
		if(StringUtil.isNotEmpty(EndTime)){
			q.and().append(" DATE_FORMAT(em.AddTime,'%Y-%m-%d')<=?", EndTime);
		}
		
		if (StringUtil.isNotEmpty(MProvince) && !StringUtil.isNotEmpty(MCity)) {
			q.append(" and em.Address like ?", "%" + MProvince + "%");
		}
		if (!StringUtil.isNotEmpty(MProvince) && StringUtil.isNotEmpty(MCity)) {
			q.append(" and em.Address like ?", "%" + MCity + "%");
		}
		if (StringUtil.isNotEmpty(MProvince) && StringUtil.isNotEmpty(MCity)) {
			q.append(" and em.Address like ?", "%" + MProvince + "%" + MCity + "%");
		}
		if (StringUtil.isNotEmpty(FullName)) {
			q.append(" and (cc.FullName like ? or em.Mobile like ?)","%" + FullName + "%","%" + FullName + "%");
		}
		q.append(" order by em.AddTime desc");
		DataTable dt = q.fetch(dga);
		dga.bindData(dt);
		dga.setTotal(q);
	}

	/**
	 * 一级账号统计
	 * 
	 * @throws ParseException
	 */
	@Priv(loginType = LoginType.ALL)
	public void findAccountVisitV() throws ParseException {
		String StartTime = $V("StartTime");// 起始时间
		String EndTime = $V("EndTime");// 结束时间
		String MProvince = $V("MProvince");// 省份
		String MCity = $V("MCity");// 城市
		String FullName = $V("FullName");// 机构
		Q qz = new Q("select DATE_FORMAT(em.AddTime,'%Y-%m-%d') , count(em.AddTime) from Ec_Member as em Left join Crm_Company as cc on em.id = cc.id join ahj_member_remark as amr on amr.ID = em.ID and amr.RemarkMember like '%eao%' where Type = 'Ent' ");
		if(StringUtil.isNotEmpty(StartTime)){
			qz.and().append(" DATE_FORMAT(em.AddTime,'%Y-%m-%d')>=?", StartTime);
		}
		if(StringUtil.isNotEmpty(EndTime)){
			qz.and().append(" DATE_FORMAT(em.AddTime,'%Y-%m-%d')<=?", EndTime);
		}
		if (StringUtil.isNotEmpty(MProvince) && !StringUtil.isNotEmpty(MCity)) {
			qz.append("and em.Address like ?", "%" + MProvince + "%");
		}
		if (!StringUtil.isNotEmpty(MProvince) && StringUtil.isNotEmpty(MCity)) {
			qz.append("and em.Address like ?", "%" + MCity + "%");
		}
		if (StringUtil.isNotEmpty(MProvince) && StringUtil.isNotEmpty(MCity)) {
			qz.append("and em.Address like ?", "%" + MProvince + "%" + MCity + "%");
		}
		if (StringUtil.isNotEmpty(FullName)) {
			qz.append(" and (cc.FullName like ? or em.Mobile like ?) ", "%" + FullName + "%","%" + FullName + "%");
		}
		qz.append(" GROUP BY DATE_FORMAT(em.AddTime,'%Y-%m-%d') ");
		DataTable histogram = qz.fetch();
		List<Map<String, String>> lists = new ArrayList<>();
		for (DataRow dataRow : histogram) {
			Map<String, String> mapOne = new HashMap<>();
			String AddTime = dataRow.get(0).toString();
			String count = dataRow.get(1).toString();
			mapOne.put("date", AddTime);
			mapOne.put("count", count);
			lists.add(mapOne);
		}
		this.Response.put("AddTime", lists);// 数量——时间（折线图，柱状图）
	}

	/**
	 * 二级账号统计
	 * 
	 * @throws ParseException
	 */
	@Priv(loginType = LoginType.ALL)
	public void findSubAccountVisit(CommonDataGridAction dga) throws ParseException {
		String StartTime = $V("StartTime");// 起始时间
		String EndTime = $V("EndTime");// 结束时间
		String CompanyInfo = $V("CompanyInfo");// 机构名称
		Q q = new Q(
				"select em.Gender,em.Mobile,mb.Name,em.UserName,em.RealName,mr.Company,cc.FullName as employeeNumber,mr.AddTime,mr.Member,ep.Type,eie.IsEngineer,coalesce(ep.Position,'游客') as Position,member.Mobile CompanyMobile "
				+ " from Mh_Resource mr "
				+ " LEFT JOIN ec_member em on mr.Member = em.ID"
				+ " LEFT JOIN crm_company cc on mr.Company = cc.ID"
				+ " LEFT JOIN ec_member member on member.ID = cc.ID"
				+ " LEFT JOIN mh_branch mb on mr.BranchInnerCode = mb.BranchInnerCode and mr.Company = mb.Company"
				+ " LEFT JOIN eao_is_engineer eie on eie.Member = mr.Member"
				+ " LEFT JOIN eao_Position as ep on ep.Id= eie.IsEngineer "
				+ " join ahj_member_remark as amr on amr.ID = em.ID and amr.RemarkMember like '%eao%'"
				+ " where mr.Company in(select ID from crm_company)"
				+ " and 1 = 1");
		if(StringUtil.isNotEmpty(StartTime)){
			q.and().append(" DATE_FORMAT(mr.AddTime,'%Y-%m-%d')>=?", StartTime);
		}
		if(StringUtil.isNotEmpty(EndTime)){
			q.and().append(" DATE_FORMAT(mr.AddTime,'%Y-%m-%d')<=?", EndTime);
		}
		
		if (StringUtil.isNotEmpty(CompanyInfo)) {
			String[] company = CompanyInfo.split(",");
			q.and().in("member.Mobile", company);
		}
		q.append(" order by AddTime desc");
		dga.bindData(q.fetch(dga));
		dga.setTotal(q);
	}

	/**
	 * 二级账号统计
	 * 
	 * @throws ParseException
	 */
	@Priv(loginType = LoginType.ALL)
	public void findSubAccountVisitV() throws ParseException {
		
		String StartTime = $V("StartTime");// 起始时间
		String EndTime = $V("EndTime");// 结束时间
		String CompanyInfo = $V("CompanyInfo");// 机构名称
		Q q = new Q("  select mr.Company , count(mr.Company) as count , CC.FullName from Mh_Resource mr LEFT JOIN ec_member em on mr.Member = em.ID LEFT JOIN mh_branch mb on mr.BranchInnerCode = mb.BranchInnerCode and mr.Company = mb.Company LEFT JOIN crm_company cc on mr.Company = cc.ID LEFT JOIN ec_member member on member.ID = cc.ID LEFT JOIN eao_is_engineer eie on eie.Member = mr.Member LEFT JOIN eao_Position as ep on ep.Id= eie.IsEngineer join ahj_member_remark as amr on amr.ID = em.ID and amr.RemarkMember like '%eao%' where mr.Company in(select ID from crm_company) ");
		Q qcount = new Q("select DATE_FORMAT(mr.AddTime,'%Y-%m-%d') , count(DATE_FORMAT(mr.AddTime,'%Y-%m-%d')) as Count from Mh_Resource mr LEFT JOIN ec_member em on mr.Member = em.ID LEFT JOIN mh_branch mb on mr.BranchInnerCode = mb.BranchInnerCode and mr.Company = mb.Company LEFT JOIN crm_company cc on mr.Company = cc.ID LEFT JOIN ec_member member on member.ID = cc.ID LEFT JOIN eao_is_engineer eie on eie.Member = mr.Member LEFT JOIN eao_Position as ep on ep.Id= eie.IsEngineer join ahj_member_remark as amr on amr.ID = em.ID and amr.RemarkMember like '%eao%' where mr.Company in(select ID from crm_company) ");
		if(StringUtil.isNotEmpty(StartTime)){
			q.and().append(" DATE_FORMAT(mr.AddTime,'%Y-%m-%d')>=?", StartTime);
			qcount.and().append(" DATE_FORMAT(mr.AddTime,'%Y-%m-%d')>=?", StartTime);
		}
		if(StringUtil.isNotEmpty(EndTime)){
			q.and().append(" DATE_FORMAT(mr.AddTime,'%Y-%m-%d')<=?", EndTime);
			qcount.and().append(" DATE_FORMAT(mr.AddTime,'%Y-%m-%d')<=?", EndTime);
		}
		if (StringUtil.isNotEmpty(CompanyInfo)) {
			String[] company = CompanyInfo.split(",");
			q.and().in("member.Mobile", company);
			qcount.and().in("member.Mobile", company);
		}
		q.append(" GROUP BY mr.Company");
		qcount.append(" GROUP BY DATE_FORMAT(mr.AddTime,'%Y-%m-%d')");
		DataTable dataTable = q.fetch();
		DataTable table = qcount.fetch();
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		for (DataRow dataRow : table) {
			Map<String, String> mapOne = new HashMap<>();
			String AddTime = dataRow.get(0).toString();
			String count = dataRow.get(1).toString();
			mapOne.put("date", AddTime);
			mapOne.put("count", count);
			list.add(mapOne);
		}
		this.Response.put("AddTime", list);// 数量——时间（折线图，柱状图）
		this.Response.put("countId", dataTable);// 机构人员占总人数比例（饼图）
	}

}
